SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Module 4 of Path: Become a Database Professional with SQL

SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Module Overview

In this module, you will cover the four types of SQL antipatterns, which include logical antipatterns, physical antipatterns, query antipatterns, and application development antipatterns. More specifically, you will be able to run the code on the platform to get insight into every issue that is being discussed in the module, and you will be able to see the solution at the end of every chapter. Once you complete the module, you will be able to recognize any database-related issue more quickly.

Module Objectives

Learn the best practices for writing SQL queries.

Get introduced to SQL antipatterns and solutions.

Increase your SQL skills by inspecting SQL antipatterns.

Learn issues corresponding to create, read, update, and delete (CRUD) operations in SQL database.

86 Lessons

214 Playgrounds

201 Code Snippets

46 Illustrations

Module 3

Contents (Module 4)

Module 3

Contents (Module 4)

1. Introduction

Conventions

Example Database

2. Logical Antipattern - Jaywalking

Synopsis: Jaywalking

Antipattern: Format Comma-Separated Lists

Solution: Create an Intersection Table

3. Logical Antipattern - Naive Trees

Synopsis: Naive Trees

Antipattern: Always Depend on One’s Parent

Maintaining a Tree with an Adjacency List

Solution: Use Alternative Tree Models

Path Enumeration

Nested Sets

Closure Table

Comparison of Different Tree Implementations

4. Logical Antipattern - ID Required

Synopsis: ID Required

Antipattern: One Size Fits All

Solution: Tailored to Fit

5. Logical Antipattern - Keyless Entry

Synopsis: Keyless Entry

Antipattern: Leave Out the Constraints

Solution: Declare Constraints

ASSESSMENT 1

Assessment: Logical Antipatterns - Part 1

7. Logical Antipattern - Entity-Attribute-Value

Synopsis: Entity-Attribute-Value

Antipattern: Use a Generic Attribute Table

Supporting Data Integrity

Solution: Model the Subtypes Using Inheritance

Suboptimal Solutions

8. Logical Antipattern - Polymorphic Associations

Synopsis: Polymorphic Associations

Antipattern: Use Dual-Purpose Foreign Key

Solution 1: Simplify the Relationship

Solution 2: Create a Common Super-Table

9. Logical Antipattern - Multicolumn Attributes

Synopsis: Multi-column Attributes

Antipattern: Create Multiple Columns

Solution: Create Dependent Table

10. Logical Antipattern - Metadata Tribbles

Synopsis: Metadata Tribbles

Antipattern: Clone Tables or Columns

Solution: Partition and Normalize

ASSESSMENT 2

Assessment: Logical Antipatterns - Part 2

12. Physical Antipattern - Rounding Errors

Synopsis: Rounding Errors

Antipattern: Use FLOAT Data Type

Solution: Use NUMERIC Data Type

13. Physical Antipattern - 31 Flavors

Synopsis: 31 Flavors

Antipattern: Specify Values in the Column Definition

Solution: Specify Values in Data

14. Physical Antipattern - Phantom Files

Synopsis: Phantom Files

Antipattern: Assume You Must Use Files

Solution: Use BLOB Data Types As Needed

15. Physical Antipattern - Index Shotgun

Synopsis: Index Shotgun

Antipattern: Using Indexes Without a Plan

Solution: MENTOR Your Indexes

ASSESSMENT 3

Assessment: Physical Antipatterns

17. Query Antipattern - Fear of the Unknown

Synopsis: Fear of the Unknown

Antipattern: Use Null as an Ordinary Value, or Vice Versa

Solution: Use NULL as a Unique Value

18. Query Antipattern - Ambiguous Groups

Synopsis: Ambiguous Groups

Antipattern: Reference Non-grouped Columns

Solution: Use Columns Unambiguously

19. Query Antipattern - Random Selection

Synopsis: Random Selection

Antipattern: Sort Data Randomly

Solution: In No Particular Order

20. Query Antipattern - Poor Man’s Search Engine

Synopsis: Poor Man’s Search Engine

Antipattern: Pattern Matching Predicates

Solution: Use the Right Tool for the Job

Alternatives To Make Text Searchable

21. Query Antipattern - Spaghetti Query

Synopsis: Spaghetti Query

Antipattern: Solve a Complex Problem in One Step

Solution: Divide and Conquer

22. Query Antipattern - Implicit Columns

Synopsis: Implicit Columns

Antipattern: A Shortcut That Gets You Lost

Solution: Name Columns Explicitly

ASSESSMENT 4

Assessment: Query Antipattern

24. Application Development Antipattern - Readable Passwords

Synopsis: Readable Passwords

Antipattern: Store Password in Plain Text

Solution: Store a Salted Hash of the Password

Other Ways to Secure Our Database Passwords

25. Application Development Antipattern - SQL Injection

Synopsis: SQL Injection

Antipattern: Execute Unverified Input As Code

The Quest for a Cure

Solution: Trust No One

26. Application Development Antipattern - Pseudokey Neat-Freak

Synopsis: Pseudo Key Neat-Freak

Antipattern: Filling in the Corners

Solution: Strictly Follow Referencing Rules

27. Application Development Antipattern - See No Evil

Synopsis: See No Evil

Antipattern: Making Bricks Without Straw

Solution: Recover from Errors Gracefully

28. Application Development Antipattern - Diplomatic Immunity

Synopsis: Diplomatic Immunity

Antipattern: Make SQL a Second-Class Citizen

Solution: Establish a Big-Tent Culture of Quality

29. Application Development Antipattern - Magic Beans

Synopsis: Magic Beans

Antipattern: The Model Is an Active Record

Solution: The Model Has an Active Record

ASSESSMENT 5

Assessment: Application Development Antipattern

31. Conclusion

Final Remarks

Module Details

86 Lessons

214 Playgrounds

201 Code Snippets

46 Illustrations

Learn in-demand tech skills in half the time

SOLUTIONS

For Enterprise

For Individuals

For HR & Recruiting

For Bootcamps

PRODUCTS

Educative Learning

Educative Onboarding

Educative Skill Assessments

Educative Projects

Pricing

For Enterprise

For Individuals

Free Trial

LEGAL

Business Terms of Service

CONTRIBUTE

Become an Author

Become an Affiliate

Become a Contributor

RESOURCES

Educative Blog

EM Hub

Educative Sessions

Educative Answers

ABOUT US

Our Team

Careers

Hiring

Frequently Asked Questions

Press

GitHub Students Scholarship

Course Catalog

Early Access Courses

Earn Referral Credits

CodingInterview.com